package com.zhucai.credit.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.zhucai.credit.model.CreditProductEntity;

/**
 * 
 * @author hsg
 *（筑材服务机构）Repository
 */
@Repository
public interface CreditProductRepository extends
		PagingAndSortingRepository<CreditProductEntity, Long>,
		JpaSpecificationExecutor<CreditProductEntity> {

	/**
	 * 根据服务机构类型查询机构信息
	 * @date 2017年11月28日 @user hsg
	 */
	CreditProductEntity findByCrProdTypeAndStatusNot(String ePoint, int delete);

	CreditProductEntity findByTidAndStatusNot(Long tid, int delete);

	/**
	 * 查询待开通的贷款机构集合
	 * @date 2017年12月16日 @user hsg
	 */
	@Query(value="select fpt.id,fpt.cr_prod_name,fpt.cr_prod_bank_name,fpt.cr_prod_type from "
			+ " finance_product_type fpt"
			+ " where fpt.status<>?1"
			+ " and fpt.id not in (?2)",nativeQuery=true)
	List<Object[]> findWaitOpen( int delete,List<Long> openList);
	
	/**
	 * 查询用户管连的融资机构信息
	 * @param delete
	 * @param waitOpemStatusList
	 * @param tid
	 * @return
	 */
	@Query(value="select * from finance_product_type fpr where fpr.id in"
			+ " (select fecp.cr_pr_type_id from finance_ent_cr_prod fecp where fecp.status<>?1 and fecp.en_cr_status in (?2) and fecp.en_acc_id = ?3 group by cr_pr_type_id)",nativeQuery=true)
	List<CreditProductEntity> findCreditProd(int delete,
			List<String> waitOpemStatusList, Long tid);

	/**
	 * 查询待开通的融资机构信息
	 * @param delete
	 * @param waitOpemStatusList
	 * @param tid
	 * @return
	 */
	@Query(value="select * from finance_product_type fpr where fpr.id not in"
			+ " (select fecp.cr_pr_type_id from finance_ent_cr_prod fecp where fecp.status<>?1 and fecp.en_acc_id = ?2 and fecp.en_cr_status ='PASS' group by cr_pr_type_id)",nativeQuery=true)
	List<CreditProductEntity> findWaitCreditProd(int delete,Long tid);

	/**
	 * 查询正常的银行产品
	 * @param delete
	 * @return
	 */
	List<CreditProductEntity> findByStatusNot(int delete);

	/**
	 * 查询用户默认银行信息
	 * @author hsg
	 * @version 2018年7月11日
	 * @param accId
	 * @param supAccId 
	 * @return
	 */
	@Query(value="select * from finance_product_type fpr where fpr.id = "
			+ "(select fao.bank_id from finance_account_operation fao where fao.acc_pur_id=?1 and fao.acc_sup_id=?2)",nativeQuery=true)
	CreditProductEntity findFinanceAccountBankInfo(Long accId, Long supAccId);





	





}
